import requests
from bs4 import BeautifulSoup
import re
import mysql.connector
from collections import Counter
from itertools import chain
from flask import Blueprint
from JsonResult import JsonResult
from flask import Flask, request, jsonify
sql = Blueprint('sql', __name__)


# 同步最新数据
@sql.route('/sql/getTableList', methods=[ "POST"])
def getTableList():
    tables=[]
    params = request.get_json()
    url = params['url']
    username = params['username']
    password = params['password']
    driverClassName = params['driverClassName']

    pattern = r'jdbc:mysql://([^:/]+):(\d+)/([^?]+)'
    # 匹配
    match = re.match(pattern, url)
    if match:
        # 提取结果
        host = match.group(1)  # 地址
        port = match.group(2)  # 端口
        db_name = match.group(3)  # 数据库名

        # 连接到 MySQL 数据库
        conn = mysql.connector.connect(
            host=f"{host}",  # 数据库主机
            user=f"{username}",  # 数据库用户名
            password=f"{password}",  # 数据库密码
            database=f"{db_name}",  # 要连接的数据库名
            port=f"{port}"
        )
        # 创建一个游标对象
        cursor = conn.cursor()
        # 执行查询
        cursor.execute(f"select table_name from information_schema.TABLES where table_schema='{db_name}'")
        # 获取结果
        result = cursor.fetchall()
        # 打印结果
        for row in result:
            print(row[0])
            tables.append({'tableName':row[0].upper()})
        # 关闭游标和连接
        cursor.close()
        conn.close()
    else:
        print("无效的 JDBC URL")

    return JsonResult.success(tables).toJsonString()


@sql.route('/sql/generateSql', methods=[ "POST"])
def generateSql():
    params = request.get_json()
    database=params['database']
    tableName=params['tableName'].lower()
    method=params['method']
    url = database['url']
    username = database['username']
    password = database['password']
    driverClassName = database['driverClassName']
    sql=''

    pattern = r'jdbc:mysql://([^:/]+):(\d+)/([^?]+)'
    # 匹配
    match = re.match(pattern, url)
    if match:
        # 提取结果
        host = match.group(1)  # 地址
        port = match.group(2)  # 端口
        db_name = match.group(3)  # 数据库名

        # 连接到 MySQL 数据库
        conn = mysql.connector.connect(
            host=f"{host}",  # 数据库主机
            user=f"{username}",  # 数据库用户名
            password=f"{password}",  # 数据库密码
            database=f"{db_name}",  # 要连接的数据库名
            port=f"{port}"
        )
        # 创建一个游标对象
        cursor = conn.cursor(dictionary=True)
        # 执行查询
        cursor.execute(f"select * from information_schema.columns where table_schema='{db_name}' and table_name='{tableName}'")
        # 获取结果
        result = cursor.fetchall()
        # 打印结果
        for row in result:
            print(row)
        sql=formSql(result,method)
        # 关闭游标和连接
        cursor.close()
        conn.close()
    else:
        print("无效的 JDBC URL")

    return JsonResult.success({'sql':sql}).toJsonString()

def formSql(result,method):
    if method=='selectById':
        return selectById(result)
    return "chenggongleba"

def selectById(result):
    prikeys=list(filter(lambda x: x['COLUMN_KEY']  == 'PRI', result))
    if len(prikeys)>1:
        return '主键包含多个键'
    prikey=prikeys[0]['COLUMN_NAME']
    return prikey

if __name__ == '__main__':
    print('hh')

